﻿using System;
using System.Collections.Generic;
using System.Collections;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using ECCSystem.EntityClass;
using ECCSystem.BizRule;

namespace ECCSystem.DataAccess
{
	class ClsEmailTemplet : DBObject 
	{
		public DataSet GetEmailTempletByID(string strID)
		{
			SqlParameter[] parameters = new SqlParameter[] { 
															new SqlParameter("@KeyID", SqlDbType.UniqueIdentifier),
															new SqlParameter("@UserID", SqlDbType.Int),
															new SqlParameter("@Flg",SqlDbType.Int)
															};
			Guid guid = new Guid(strID);
			parameters[0].Value = guid;
			parameters[1].Value = ClsCommon.gIntOperatorID;
			if (ClsCommon.gStrRight != "Admin")
			{
				parameters[2].Value = 0;
			}
			else
			{
				parameters[2].Value = 1;
			}
			return RunProcedure("sp_EmailTemplet_GetListByID", parameters, "tEmailTemplet");
		}


		public DataSet GetEmailTempletByName(string strName)
		{
			SqlParameter[] parameters = new SqlParameter[] { 
															new SqlParameter("@Where", SqlDbType.NVarChar,1000)
															};
			parameters[0].Value = strName;
			return RunProcedure("sp_EmailTemplet_GetListByName", parameters, "tEmailTemplet");
		}


		public string AddEmailTemplet(ArrayList objArr,SqlTransaction objTran)
		{
			try
			{

				SqlCommand objCmd = new SqlCommand("sp_EmailTemplet_Add", BizRule.ClsCommon.gObjConn);
				int rowsAffected = 0;
				for (int i = 0; i < objArr.Count; i++)
				{
					EmailTempletEntity objEntity = new EmailTempletEntity();
					objEntity = (EmailTempletEntity)objArr[i];

					SqlParameter[] parameters = new SqlParameter[] { 
																new SqlParameter("@ContentKeyID", SqlDbType.UniqueIdentifier),
																new SqlParameter("@KeyID", SqlDbType.UniqueIdentifier),
																new SqlParameter("@Name", SqlDbType.NVarChar,100),
																new SqlParameter("@UpdatePerson", SqlDbType.Int),
																new SqlParameter("@CreatePerson", SqlDbType.Int),
																new SqlParameter("@PcContent", SqlDbType.NText),
																new SqlParameter("@ToteContent", SqlDbType.NText),
																new SqlParameter("@Negotiation", SqlDbType.NText),
																new SqlParameter("@TimeLimited", SqlDbType.Bit),
																new SqlParameter("@LimitedStartDate", SqlDbType.DateTime),
																new SqlParameter("@LimitedEndDate", SqlDbType.DateTime),
																new SqlParameter("@AvailabilitySign", SqlDbType.Bit),
																new SqlParameter("@CreateDate", SqlDbType.DateTime),
																new SqlParameter("@UpdateDate", SqlDbType.DateTime),
																new SqlParameter("@Order", SqlDbType.BigInt),
																new SqlParameter("@Attribute", SqlDbType.Bit)
																	};
					Guid guid = new Guid(objEntity.ContentKeyID);
					parameters[0].Value = guid;
					guid = new Guid(objEntity.KeyID);
					parameters[1].Value = guid;
					parameters[2].Value = objEntity.Name;
					if (objEntity.UpdatePerson == null)
					{
						parameters[3].Value = System.DBNull.Value;
					}
					else
					{
						parameters[3].Value = objEntity.UpdatePerson.Value;
					}

					if (objEntity.CreatePerson == null)
					{
						parameters[4].Value = System.DBNull.Value;
					}
					else
					{
						parameters[4].Value = objEntity.CreatePerson.Value;
					}

					parameters[5].Value = objEntity.PcContent;
					parameters[6].Value = objEntity.ToteContent;
					parameters[7].Value = objEntity.Negotiation;
					parameters[8].Value = objEntity.TimeLimited;

					if (objEntity.LimitedStartDate == null)
					{
						parameters[9].Value = System.DBNull.Value;
					}
					else
					{
						parameters[9].Value = objEntity.LimitedStartDate.Value;
					}


					if (objEntity.LimitedEndDate == null)
					{
						parameters[10].Value = System.DBNull.Value;
					}
					else
					{
						parameters[10].Value = objEntity.LimitedEndDate.Value;
					}

					parameters[11].Value = objEntity.AvailabilitySign;

					if (objEntity.CreateDate == null)
					{
						parameters[12].Value = System.DBNull.Value;
					}
					else
					{
						parameters[12].Value = objEntity.CreateDate.Value;
					}


					if (objEntity.UpdateDate == null)
					{
						parameters[13].Value = System.DBNull.Value;
					}
					else
					{
						parameters[13].Value = objEntity.UpdateDate.Value;
					}

					parameters[14].Value = objEntity.Order.Value;

					parameters[15].Value = objEntity.Attribute;

					this.RunProcedure("sp_EmailTemplet_Add", parameters, ref rowsAffected, objTran, objCmd);

					if (rowsAffected == 0)
					{
						objTran.Rollback();
						return "1";
					}
				}
				objTran.Commit();
				return "";
			}
			catch(Exception ex)
			{
				objTran.Rollback();
				return ex.ToString();
			}


		}
		public string DeleteEmailTemplet(string lngID)
		{
			try
			{
				SqlParameter[] parameters = new SqlParameter[] { new SqlParameter("@KeyID", SqlDbType.UniqueIdentifier) };
				Guid guid = new Guid(lngID);
				parameters[0].Value = guid;
				int rowsAffected = 0;
				this.RunProcedure("sp_EmailTemplet_Delete", parameters, ref rowsAffected);
				return "";
			}
			catch(Exception ex)
			{
				return ex.ToString();
			}
		}

		public string DeleteEmailTemplet(string[] lngID, SqlTransaction objTran)
		{
			try
			{
				for (int i = 0; i < lngID.Length; i++)
				{
					SqlCommand objCmd = new SqlCommand("sp_EmailTemplet_Add", BizRule.ClsCommon.gObjConn);
					SqlParameter[] parameters = new SqlParameter[] { new SqlParameter("@KeyID", SqlDbType.UniqueIdentifier) };
					Guid guid = new Guid(lngID[i]);
					parameters[0].Value = guid;
					int rowsAffected = 0;
					this.RunProcedure("sp_EmailTemplet_Delete", parameters, ref rowsAffected);
				}

				objTran.Commit();
				return "";
			}
			catch (Exception ex)
			{
				objTran.Rollback();
				return ex.ToString();
			}
		}

		public string UpdateEmailTemplet(EmailTempletEntity objEntity)
		{
			try
			{
				SqlParameter[] parameters = new SqlParameter[] { 
																new SqlParameter("@ContentKeyID", SqlDbType.UniqueIdentifier),
																new SqlParameter("@KeyID", SqlDbType.UniqueIdentifier),
																new SqlParameter("@Name", SqlDbType.NVarChar,100),
																new SqlParameter("@UpdatePerson", SqlDbType.Int),
																new SqlParameter("@CreatePerson", SqlDbType.Int),
																new SqlParameter("@PcContent", SqlDbType.NText),
																new SqlParameter("@ToteContent", SqlDbType.NText),
																new SqlParameter("@Negotiation", SqlDbType.NText),
																new SqlParameter("@TimeLimited", SqlDbType.Bit),
																new SqlParameter("@LimitedStartDate", SqlDbType.DateTime),
																new SqlParameter("@LimitedEndDate", SqlDbType.DateTime),
																new SqlParameter("@AvailabilitySign", SqlDbType.Bit),
																new SqlParameter("@CreateDate", SqlDbType.DateTime),
																new SqlParameter("@UpdateDate", SqlDbType.DateTime),
																new SqlParameter("@Order", SqlDbType.BigInt),
																new SqlParameter("@Attribute", SqlDbType.Bit)
																	};
				Guid guid = new Guid(objEntity.ContentKeyID);
				parameters[0].Value = guid;
				guid = new Guid(objEntity.KeyID);
				parameters[1].Value = guid;
				parameters[2].Value = objEntity.Name;
				if (objEntity.UpdatePerson == null)
				{
					parameters[3].Value = System.DBNull.Value;
				}
				else
				{
					parameters[3].Value = objEntity.UpdatePerson.Value;
				}

				if (objEntity.CreatePerson == null)
				{
					parameters[4].Value = System.DBNull.Value;
				}
				else
				{
					parameters[4].Value = objEntity.CreatePerson.Value;
				}

				parameters[5].Value = objEntity.PcContent;
				parameters[6].Value = objEntity.ToteContent;
				parameters[7].Value = objEntity.Negotiation;
				parameters[8].Value = objEntity.TimeLimited;

				if (objEntity.LimitedStartDate == null)
				{
					parameters[9].Value = System.DBNull.Value;
				}
				else
				{
					parameters[9].Value = objEntity.LimitedStartDate.Value;
				}


				if (objEntity.LimitedEndDate == null)
				{
					parameters[10].Value = System.DBNull.Value;
				}
				else
				{
					parameters[10].Value = objEntity.LimitedEndDate.Value;
				}

				parameters[11].Value = objEntity.AvailabilitySign;

				if (objEntity.CreateDate == null)
				{
					parameters[12].Value = System.DBNull.Value;
				}
				else
				{
					parameters[12].Value = objEntity.CreateDate.Value;
				}


				if (objEntity.UpdateDate == null)
				{
					parameters[13].Value = System.DBNull.Value;
				}
				else
				{
					parameters[13].Value = objEntity.UpdateDate.Value;
				}

				parameters[14].Value = objEntity.Order.Value;

				parameters[15].Value = objEntity.Attribute;
				int rowsAffected = 0;

				this.RunProcedure("sp_EmailTemplet_Update", parameters, ref rowsAffected);

				if (rowsAffected == 0)
				{
					return "1";
				}
				return "";
			}
			catch (Exception ex)
			{
				return ex.ToString();
			}
		}

		public string ImportExcel(ArrayList objALKindOfTempletEntity, ArrayList objALEmailTempletEntity, SqlTransaction objTran)
		{
			try
			{
				SqlCommand objCmd = new SqlCommand("Import", BizRule.ClsCommon.gObjConn);
				int rowsAffected = 0;

				for (int i = 0; i < objALKindOfTempletEntity.Count; i++)
				{
					KindOfTempletEntity objEntityTmp = new KindOfTempletEntity();
					objEntityTmp = (KindOfTempletEntity)objALKindOfTempletEntity[i];

					SqlParameter[] parametersTmp = new SqlParameter[] { 
															new SqlParameter("@Name", SqlDbType.NVarChar,100)
															};
					parametersTmp[0].Value = objEntityTmp.Name;
					objCmd.CommandText = "sp_KindOfTemplet_GetListByWhere";
					int intRet = this.RunProcedure("sp_KindOfTemplet_GetListByWhere", parametersTmp, ref rowsAffected, objTran, objCmd);
					//DataSet dsKindOfTemplet = RunProcedure("sp_KindOfTemplet_GetListByWhere", parametersTmp, "KindOfTemplet", objTran, objCmd);

					//if (dsKindOfTemplet.Tables.Count > 0 && dsKindOfTemplet.Tables[0].Rows.Count > 0)
					//{
					//    break;
					//}

					if (intRet <= 0)
					{

						SqlParameter[] parameters = new SqlParameter[] { new SqlParameter("@KeyID", SqlDbType.UniqueIdentifier),
																	new SqlParameter("@ID", SqlDbType.NVarChar,1000),
																 new SqlParameter("@Name", SqlDbType.NVarChar, 50),
																 new SqlParameter("@ParentNode", SqlDbType.NVarChar,1000),
																 new SqlParameter("@Oreder", SqlDbType.BigInt),
																new SqlParameter("@UserID", SqlDbType.Int),
																new SqlParameter("@PersonalFlg", SqlDbType.BigInt) };
						Guid guid = new Guid(objEntityTmp.KeyID);
						parameters[0].Value = guid;
						parameters[1].Value = objEntityTmp.ID;
						parameters[2].Value = objEntityTmp.Name;
						parameters[3].Value = objEntityTmp.ParentNode;
						parameters[4].Value = objEntityTmp.Oreder;
						parameters[5].Value = ClsCommon.gIntOperatorID;
						if (ClsCommon.gStrRight == "CSR")
						{
							parameters[6].Value = 1;
						}
						else
						{
							parameters[6].Value = 0;
						}
						objCmd.CommandText = "sp_KindOfTemplet_Add";

						this.RunProcedure("sp_KindOfTemplet_Add", parameters, ref rowsAffected, objTran, objCmd);

						//if (rowsAffected == 0)
						//{
						//    objTran.Rollback();
						//    return "1";
						//}
					}
				}
				rowsAffected = 0;

				for (int i = 0; i < objALEmailTempletEntity.Count; i++)
				{
					int intRet = 0;
					EmailTempletEntity objEntity = new EmailTempletEntity();
					objEntity = (EmailTempletEntity)objALEmailTempletEntity[i];
					#region
					//SqlParameter[] parametersTmp = new SqlParameter[] { 
					//                                             new SqlParameter("@Order", SqlDbType.BigInt) };
					//objCmd.Transaction = null;
					//parametersTmp[0].Value = objEntity.Order;
					//objCmd.CommandText = "sp_EmailTemplet_GetListByWhere";
					//DataSet objDataSet = this.RunProcedure("sp_EmailTemplet_GetListByWhere", parametersTmp, "EmailTemplet", objTran, objCmd);

					//if (objDataSet.Tables.Count > 0 && objDataSet.Tables[0].Rows.Count > 0)
					//{
					//    if (objDataSet.Tables[0].Rows[0]["EmailTemplet_Name"].ToString().Trim() != objEntity.Name.Trim())
					//    {
					//        intRet = 1;
					//    }

					//    if (objDataSet.Tables[0].Rows[0]["EmailContent_PcContent"].ToString().Trim() != objEntity.PcContent.Trim())
					//    {
					//        intRet = 1;
					//    }

					//    if (objDataSet.Tables[0].Rows[0]["EmailContent_ToteContent"].ToString().Trim() != objEntity.ToteContent.Trim())
					//    {
					//        intRet = 1;
					//    }

					//    if (objDataSet.Tables[0].Rows[0]["EmailContent_Negotiation"].ToString().Trim() != objEntity.Negotiation.Trim())
					//    {
					//        intRet = 1;
					//    }
					//}
					//else
					//{
					//    intRet = -1;
					//}
					#endregion

					SqlParameter[] parameters = new SqlParameter[] { 
																new SqlParameter("@ContentKeyID", SqlDbType.UniqueIdentifier),
																new SqlParameter("@KeyID", SqlDbType.UniqueIdentifier),
																new SqlParameter("@Name", SqlDbType.NVarChar,100),
																new SqlParameter("@UpdatePerson", SqlDbType.Int),
																new SqlParameter("@CreatePerson", SqlDbType.Int),
																new SqlParameter("@PcContent", SqlDbType.NText),
																new SqlParameter("@ToteContent", SqlDbType.NText),
																new SqlParameter("@Negotiation", SqlDbType.NText),
																new SqlParameter("@TimeLimited", SqlDbType.Bit),
																new SqlParameter("@LimitedStartDate", SqlDbType.DateTime),
																new SqlParameter("@LimitedEndDate", SqlDbType.DateTime),
																new SqlParameter("@AvailabilitySign", SqlDbType.Bit),
																new SqlParameter("@CreateDate", SqlDbType.DateTime),
																new SqlParameter("@UpdateDate", SqlDbType.DateTime),
																new SqlParameter("@Order", SqlDbType.BigInt),
																new SqlParameter("@Attribute", SqlDbType.Bit),
																new SqlParameter("@KeyName",SqlDbType.NVarChar,100)
																	};
					Guid guid = new Guid(objEntity.ContentKeyID);
					parameters[0].Value = guid;
					guid = new Guid(objEntity.KeyID);
					parameters[1].Value = guid;
					parameters[2].Value = objEntity.Name;
					if (objEntity.UpdatePerson == null)
					{
						parameters[3].Value = System.DBNull.Value;
					}
					else
					{
						parameters[3].Value = objEntity.UpdatePerson.Value;
					}

					if (objEntity.CreatePerson == null)
					{
						parameters[4].Value = System.DBNull.Value;
					}
					else
					{
						parameters[4].Value = objEntity.CreatePerson.Value;
					}

					parameters[5].Value = objEntity.PcContent;
					parameters[6].Value = objEntity.ToteContent;
					parameters[7].Value = objEntity.Negotiation;
					parameters[8].Value = objEntity.TimeLimited;

					if (objEntity.LimitedStartDate == null)
					{
						parameters[9].Value = System.DBNull.Value;
					}
					else
					{
						parameters[9].Value = objEntity.LimitedStartDate.Value;
					}


					if (objEntity.LimitedEndDate == null)
					{
						parameters[10].Value = System.DBNull.Value;
					}
					else
					{
						parameters[10].Value = objEntity.LimitedEndDate.Value;
					}

					parameters[11].Value = objEntity.AvailabilitySign;

					if (objEntity.CreateDate == null)
					{
						parameters[12].Value = System.DBNull.Value;
					}
					else
					{
						parameters[12].Value = objEntity.CreateDate.Value;
					}


					if (objEntity.UpdateDate == null)
					{
						parameters[13].Value = System.DBNull.Value;
					}
					else
					{
						parameters[13].Value = objEntity.UpdateDate.Value;
					}

					parameters[14].Value = objEntity.Order.Value;
					if (ClsCommon.gStrRight == "CSR")
					{
						parameters[15].Value = 1;
					}
					else
					{
						parameters[15].Value = 0;
					}
					
					parameters[16].Value = objEntity.KeyName;
					
					objCmd.CommandText = "sp_EmailTemplet_Import";
					intRet = this.RunProcedure("sp_EmailTemplet_Import", parameters, ref rowsAffected, objTran, objCmd);
					#region
					/*
					if (intRet < 0)
					{
						SqlParameter[] parameters = new SqlParameter[] { 
																new SqlParameter("@ContentKeyID", SqlDbType.UniqueIdentifier),
																new SqlParameter("@KeyID", SqlDbType.UniqueIdentifier),
																new SqlParameter("@Name", SqlDbType.NVarChar,100),
																new SqlParameter("@UpdatePerson", SqlDbType.Int),
																new SqlParameter("@CreatePerson", SqlDbType.Int),
																new SqlParameter("@PcContent", SqlDbType.NText),
																new SqlParameter("@ToteContent", SqlDbType.NText),
																new SqlParameter("@Negotiation", SqlDbType.NText),
																new SqlParameter("@TimeLimited", SqlDbType.Bit),
																new SqlParameter("@LimitedStartDate", SqlDbType.DateTime),
																new SqlParameter("@LimitedEndDate", SqlDbType.DateTime),
																new SqlParameter("@AvailabilitySign", SqlDbType.Bit),
																new SqlParameter("@CreateDate", SqlDbType.DateTime),
																new SqlParameter("@UpdateDate", SqlDbType.DateTime),
																new SqlParameter("@Order", SqlDbType.BigInt),
																new SqlParameter("@Attribute", SqlDbType.Bit)
																	};
						Guid guid = new Guid(objEntity.ContentKeyID);
						parameters[0].Value = guid;
						guid = new Guid(objEntity.KeyID);
						parameters[1].Value = guid;
						parameters[2].Value = objEntity.Name;
						if (objEntity.UpdatePerson == null)
						{
							parameters[3].Value = System.DBNull.Value;
						}
						else
						{
							parameters[3].Value = objEntity.UpdatePerson.Value;
						}

						if (objEntity.CreatePerson == null)
						{
							parameters[4].Value = System.DBNull.Value;
						}
						else
						{
							parameters[4].Value = objEntity.CreatePerson.Value;
						}

						parameters[5].Value = objEntity.PcContent;
						parameters[6].Value = objEntity.ToteContent;
						parameters[7].Value = objEntity.Negotiation;
						parameters[8].Value = objEntity.TimeLimited;

						if (objEntity.LimitedStartDate == null)
						{
							parameters[9].Value = System.DBNull.Value;
						}
						else
						{
							parameters[9].Value = objEntity.LimitedStartDate.Value;
						}


						if (objEntity.LimitedEndDate == null)
						{
							parameters[10].Value = System.DBNull.Value;
						}
						else
						{
							parameters[10].Value = objEntity.LimitedEndDate.Value;
						}

						parameters[11].Value = objEntity.AvailabilitySign;

						if (objEntity.CreateDate == null)
						{
							parameters[12].Value = System.DBNull.Value;
						}
						else
						{
							parameters[12].Value = objEntity.CreateDate.Value;
						}


						if (objEntity.UpdateDate == null)
						{
							parameters[13].Value = System.DBNull.Value;
						}
						else
						{
							parameters[13].Value = objEntity.UpdateDate.Value;
						}

						parameters[14].Value = objEntity.Order.Value;
						parameters[15].Value = 0;
						objCmd.CommandText = "sp_EmailTemplet_Add";
						this.RunProcedure("sp_EmailTemplet_Add", parameters, ref rowsAffected, objTran, objCmd);

						if (rowsAffected == 0)
						{
							objTran.Rollback();
							return "1";
						}
					}
					else if (intRet > 0)
					{
						SqlParameter[] parameters = new SqlParameter[] { 
																new SqlParameter("@ContentKeyID", SqlDbType.UniqueIdentifier),
																new SqlParameter("@KeyID", SqlDbType.UniqueIdentifier),
																new SqlParameter("@Name", SqlDbType.NVarChar,100),
																new SqlParameter("@UpdatePerson", SqlDbType.Int),
																new SqlParameter("@CreatePerson", SqlDbType.Int),
																new SqlParameter("@PcContent", SqlDbType.NText),
																new SqlParameter("@ToteContent", SqlDbType.NText),
																new SqlParameter("@Negotiation", SqlDbType.NText),
																new SqlParameter("@TimeLimited", SqlDbType.Bit),
																new SqlParameter("@LimitedStartDate", SqlDbType.DateTime),
																new SqlParameter("@LimitedEndDate", SqlDbType.DateTime),
																new SqlParameter("@AvailabilitySign", SqlDbType.Bit),
																new SqlParameter("@CreateDate", SqlDbType.DateTime),
																new SqlParameter("@UpdateDate", SqlDbType.DateTime),
																new SqlParameter("@Order", SqlDbType.BigInt)
																	};
						Guid guid = new Guid(objEntity.ContentKeyID);
						parameters[0].Value = guid;
						guid = new Guid(objEntity.KeyID);
						parameters[1].Value = guid;
						parameters[2].Value = objEntity.Name;
						if (objEntity.UpdatePerson == null)
						{
							parameters[3].Value = System.DBNull.Value;
						}
						else
						{
							parameters[3].Value = objEntity.UpdatePerson.Value;
						}

						if (objEntity.CreatePerson == null)
						{
							parameters[4].Value = System.DBNull.Value;
						}
						else
						{
							parameters[4].Value = objEntity.CreatePerson.Value;
						}

						parameters[5].Value = objEntity.PcContent;
						parameters[6].Value = objEntity.ToteContent;
						parameters[7].Value = objEntity.Negotiation;
						parameters[8].Value = objEntity.TimeLimited;

						if (objEntity.LimitedStartDate == null)
						{
							parameters[9].Value = System.DBNull.Value;
						}
						else
						{
							parameters[9].Value = objEntity.LimitedStartDate.Value;
						}


						if (objEntity.LimitedEndDate == null)
						{
							parameters[10].Value = System.DBNull.Value;
						}
						else
						{
							parameters[10].Value = objEntity.LimitedEndDate.Value;
						}

						parameters[11].Value = objEntity.AvailabilitySign;

						if (objEntity.CreateDate == null)
						{
							parameters[12].Value = System.DBNull.Value;
						}
						else
						{
							parameters[12].Value = objEntity.CreateDate.Value;
						}


						if (objEntity.UpdateDate == null)
						{
							parameters[13].Value = System.DBNull.Value;
						}
						else
						{
							parameters[13].Value = objEntity.UpdateDate.Value;
						}

						parameters[14].Value = objEntity.Order.Value;

						parameters[15].Value = objEntity.Attribute;
						rowsAffected = 0;

						objCmd.CommandText = "sp_EmailTemplet_Update";
						this.RunProcedure("sp_EmailTemplet_Update", parameters, ref rowsAffected, objTran, objCmd);

						if (rowsAffected == 0)
						{
							objTran.Rollback();
							return "1";
						}
					}*/
					#endregion

				}
				objTran.Commit();
				return "";
			}
			catch(Exception ex)
			{
				objTran.Rollback();
				return ex.ToString();
			}
		}
	}
}
